package com.hnkywl.sts.dao.zjgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.zjgl.Bzj;
import com.hnkywl.sts.entity.zjgl.Xyz;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.oracore.OracleType;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 14-4-21
 * Time: 上午10:01
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BzjDao extends SimpleHibernateTemplate<Bzj, Long> {

    public Page<Bzj> findBzjList(Long gsId, String khMc, String hth, Date createDate, int pageNo, int pageSize) {
        String hql;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Bzj> page = new Page<Bzj>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (gsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" nbgs.id = ? ");
            param.add(gsId);
        }
        if (khMc != null && !("").equals(khMc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" kh.qc like ?");
            param.add("%" + khMc.trim() + "%");
        }
        if (hth != null && !("").equals(hth)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cght.htbm like ?");
            param.add("%" + hth.trim() + "%");
        }
        if (createDate != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" date_created >= ? ");
            param.add(createDate);
        }
        //==============记录数量===============
        hql = "select count(*) from Bzj where 1=1" + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Bzj where 1=1" + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by dateCreated desc ";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public List<Bzj> findBzjblByhtid(long id) {
        String sql = "select b.bzjbl from t_biz_bzj b where b.ht_id = ?";
        List list = createSQLQuery(sql, id).list();
        if (list.size() > 0) {
            return list;
        }
        return null;
    }

    public List<Bzj> checkHtAndXyByBzj(Long htId, Long xyId) {
        return this.find("from Bzj where kh.id = ? and cght.id = ? ", xyId, htId);
    }


    public String sumJeFromCghtByKh(Long cghtId, Long khId) {
        String sql = "select nvl(sum(mx.jhsl * (select htmx.hsj\n" +
                "                    from t_biz_htmx htmx \n" +
                "                    left join t_sys_gg wz\n" +
                "                      on htmx.wz_id = wz.id\n" +
                "                   where wz.id = gg.id and htmx.ht_id = cght.id)),0) hj\n" +
                "  from t_biz_bgjh jh\n" +
                "  left join t_biz_bgjhmx mx\n" +
                "    on jh.id = mx.bgjh_id\n" +
                "  left join t_sys_gg gg\n" +
                "    on mx.wzgg_id = gg.id\n" +
                "  left join t_biz_bgjhhz hz\n" +
                "    on jh.bgjhhz_id = hz.id\n" +
                "  left join t_biz_ht cght\n" +
                "    on hz.id = cght.bgjhhz_id\n" +
                " where jh.jhdw_id = ?\n" +
                "   and cght.id = ?\n" +
                "   and cght.type = 'cght'";
        List list = createSQLQuery(sql, khId, cghtId).list();
        return list.size() == 0 ? "" : list.get(0).toString();
    }

    public String sumJeFromBzjByKh(Long cghtId, Long khId) {
        String sql = "select sum(je) from t_biz_bzj where ht_id = ? and kh_id = ?";
        List list = createSQLQuery(sql, cghtId, khId).list();
        return list.size() == 0 ? "" : list.get(0).toString();
    }

    public Bzj findBzjByCghtId(Long id) {
        String hql = " from Bzj where ht_id = ? ";
        return this.find(hql, id).size() <= 0 ? null : (Bzj) this.find(hql, id).get(0);
    }


    /**
     * 根据采购合同找出对应的保证金集合
     *
     * @param cghtId
     * @return
     */
    public List<Bzj> findBzjListByCghtId(Long cghtId) {
        String hql = "from Bzj where ht_id = ?";
        return this.find(hql, cghtId);
    }

    public Bzj findUniqueBzj(Long htId, Long sfkId) {
        String hql = " from Bzj where ht_id = ? and sk_id = ?";
        return this.find(hql, htId, sfkId).size() <= 0 ? null : (Bzj) this.find(hql, htId, sfkId).get(0);
    }

    /**
     * 交易系统->ESB 查询保证金
     *
     * @param khqc 客户全称
     * @return
     */
    public String queryMarginByKhqc(String khqc) {
        String sql = "SELECT nvl(SUM(SFK.YE),0) as ye FROM T_BIZ_SFK SFK LEFT JOIN T_SYS_GSJG GSJG ON GSJG.ID = SFK.GLDW_ID" +
                "  WHERE SFK.KXSY_SJZDZ_ID in (121491,121791,122091) AND sfk.zt='ytz' AND gsjg.qc = ?";
        List list = createSQLQuery(sql, khqc).list();
        return list.get(0).toString();
    }

    /**
     * 查询保证金比例
     *
     * @param khqc 客户全称
     * @return
     */
    public String queryBzjblByKhqc(String khqc) {
        String sql = "SELECT t.bzjbl from T_SYS_GSJG t WHERE t.qc = ?";
        List list = createSQLQuery(sql, khqc).list();
        if (null != list && list.size() > 0) {
            return list.get(0).toString();
        } else {
            return "0";
        }
    }

    public Bzj getbzj(Long id) {
        String hql = " from Bzj where ht_id = ? ";
        return this.find(hql, id).size() <= 0 ? null : (Bzj) this.find(hql, id).get(0);
    }
}
